﻿using System;
using Gumini.JulaDB.Provider;
using Gumini.JulaDB.Test.Common;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Gumini.JulaDB.Test
{
    /// <summary>
    /// A test for the ORDER BY clause.
    /// </summary>
    [TestClass]
    public class OrderByTest
    {
        private static JulaDbConnection _connection;
        private static PersonTestDataCreator _peopleData;

        [ClassInitialize]
        public static void InitializeDatabase(TestContext context)
        {
            _connection = TestUtils.CreateTestDatabase();
            _peopleData = new PersonTestDataCreator(_connection);
        }

        [ClassCleanup]
        public static void CleanupDatabase()
        {
            _connection.Close();
        }

        /// <summary>
        /// ORDER BY a single column without specifying sort order. Ascending should be used by default.
        /// </summary>
        [TestMethod]
        public void OrderByDefault()
        {
            var result = _connection.ExecuteValidSelect("select FirstName, LastName from Person order by age");
            result.CheckRowValues(0, _peopleData.YoungestPersonFirstName, _peopleData.YoungestPersonLastName);
        }

        /// <summary>
        /// ORDER BY a single column when specifying ascending sort order explicitly.
        /// </summary>
        [TestMethod]
        public void OrderByAscending()
        {
            var result = _connection.ExecuteValidSelect("select FirstName, LastName from Person order by age asc");
            result.CheckRowValues(0, _peopleData.YoungestPersonFirstName, _peopleData.YoungestPersonLastName);
        }

        /// <summary>
        /// ORDER BY a single column when specifying descending sort order explicitly.
        /// </summary>
        [TestMethod]
        public void OrderByDescending()
        {
            var result = _connection.ExecuteValidSelect("select FirstName, LastName from Person order by age desc");
            result.CheckRowValues(0, "Tomasz", "Kowalski");
        }

        /// <summary>
        /// ORDER BY descending with a WHERE condition.
        /// </summary>
        [TestMethod]
        public void OrderByDescendingNotNull()
        {
            var result = _connection.ExecuteValidSelect(
              @"select FirstName, LastName
                from Person
                where age is not null
                order by age desc");
            result.CheckRowValues(0, _peopleData.OldestPersonFirstName, _peopleData.OldestPersonLastName);
        }

        /// <summary>
        /// ORDER BY two columns, first ascending, second one descending.
        /// </summary>
        [TestMethod]
        public void OrderByTwoColumns()
        {
            var result = _connection.ExecuteValidSelect(
              @"select FirstName, LastName, Age
                from Person
                order by LastName asc, Age desc");

            result.CheckRowValues(0, "Maria", "Kowalska", 11.5M);
            result.CheckRowValues(1, "Tomasz", "Kowalski", DBNull.Value);
            result.CheckRowValues(2, "Marek", "Kowalski", 45M);
            result.CheckRowValues(3, "Jan", "Kowalski", 12.4M);
            result.CheckRowValues(4, "Tomasz", "Nowak", 72.7M);
            result.CheckRowValues(5, "Jan", "Nowak", 55.9M);
            result.CheckRowValues(6, "Anna", "Nowak", 24.1M);
        }
    }
}
